package person.crud.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.Map;

import javax.annotation.PostConstruct;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import person.crud.filter.PersonCrud;
import person.model.Person;

@Repository
public class PersonDAO
{
    private Connection conn;

    public Connection getConn()
    {
	return conn;
    }

    public void setConn(Connection conn)
    {
	this.conn = conn;
    }

    @Transactional
    public boolean add(Person p) throws SQLException
    {
	boolean res = PersonCrud.filter(p);
	// clear Person from possible sql inject
	PersonCrud.clear(p);

	LinkedHashMap<String, String> data = getRequestValues(p);

	if (res && data.size() > 0)
	{
	    int i = 0;

	    String fields = "INSERT INTO users (", values = " VALUES (";

	    for (Map.Entry<String, String> entry : data.entrySet())
	    {
		if (i < data.size() - 1)
		{
		    fields += entry.getKey() + ',';
		    values += "?,";
		}
		else
		{
		    fields += entry.getKey();
		    values += "?";
		}
		i++;

	    }

	    fields += ')';
	    values += ')';
	    PreparedStatement ps = conn.prepareStatement(fields + values);

	    i = 1;
	    for (Map.Entry<String, String> entry : data.entrySet())
	    {
		ps.setString(i, entry.getValue());
		i++;
	    }
	    try
	    {

		ps.executeUpdate();

	    } catch (SQLException e)
	    {
		throw new RuntimeException(e);

	    }
	}
	else
	{
	    res = false;
	}
	return res;
    }

    @Transactional
    public boolean update(Person p) throws SQLException
    {
	boolean res = PersonCrud.filter(p);
	// clear Person from possible sql inject
	PersonCrud.clear(p);

	LinkedHashMap<String, String> data = getRequestValues(p);

	if (res && data.size() > 0 && data.get("id") != null)
	{
	    String sql = "UPDATE users SET ";

	    int i = 0;

	    for (Map.Entry<String, String> entry : data.entrySet())
	    {
		if (!entry.getKey().equals("id"))
		{
		    if (i < data.size() - 1)
		    {

			sql += entry.getKey() + "=?,";
		    }
		    else
		    {
			sql += entry.getKey() + "=?";
		    }
		}

		i++;
	    }

	    sql += " WHERE id=?";
	    // System.out.println(sql + "  size:" + data.size());
	    PreparedStatement ps = conn.prepareStatement(sql);

	    i = 1;

	    for (Map.Entry<String, String> entry : data.entrySet())
	    {
		if (entry.getKey().equals("id"))
		{
		    ps.setString(data.size(), data.get("id"));
		    continue;
		}
		else
		{
		    ps.setString(i, entry.getValue());
		}

		i++;
	    }

	    try
	    {
		ps.executeUpdate();

	    } catch (SQLException e)
	    {
		throw new RuntimeException(e);

	    }
	}
	else
	{
	    res = false;
	}
	return res;

    }

    @Transactional
    public LinkedList<Person> getCombined(Person person) throws SQLException
    {
	LinkedHashMap<String, String> data = getRequestValues(person);

	LinkedList<Person> res = new LinkedList<Person>();

	if (data.size() > 0)
	{
	    String sql = "";
	    int i = 1;

	    sql = "SELECT * FROM users WHERE ";

	    for (Map.Entry<String, String> entry : data.entrySet())
	    {
		if (entry.getKey().equals("id") || entry.getKey().equals("age"))
		{
		    sql += entry.getKey() + "=?";
		}
		else
		{
		    sql += entry.getKey() + " LIKE ? ";
		}

		if (i < data.size())
		{
		    sql += " AND ";
		}
		i++;

	    }
	   // System.out.println(sql + " size:" + data.size());
	    PreparedStatement ps = conn.prepareStatement(sql);

	    i = 1;
	    for (Map.Entry<String, String> entry : data.entrySet())
	    {
		if (entry.getKey().equals("id") || entry.getKey().equals("age"))
		{
		    ps.setString(i, entry.getValue());
		}
		else
		{
		    ps.setString(i, "%" + entry.getValue() + "%");
		}

		i++;

	    }

	    try
	    {
		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
		    res.add(new Person(rs.getInt("id"), rs.getInt("age"), rs
			    .getString("FirstName"), rs.getString("LastName"),
			    rs.getString("sex")));

		}

	    } catch (SQLException e)
	    {
		throw new RuntimeException(e);

	    }

	}

	return res;

    }

    @Transactional
    public LinkedList<Person> getAll()
    {
	String sql = "SELECT * FROM users LIMIT 50";

	LinkedList<Person> res = new LinkedList<Person>();
	try
	{
	    PreparedStatement ps = conn.prepareStatement(sql);
	    ResultSet rs = ps.executeQuery();

	    while (rs.next())
	    {
		res.add(new Person(rs.getInt("id"), rs.getInt("age"), rs
			.getString("FirstName"), rs.getString("LastName"), rs
			.getString("sex")));

	    }

	} catch (SQLException e)
	{
	    throw new RuntimeException(e);
	}

	return res;

    }

    private LinkedHashMap<String, String> getRequestValues(Person person)
    {
	LinkedHashMap<String, String> res = new LinkedHashMap<String, String>();

	if (person.getId() != 0)
	{
	    res.put("id", String.valueOf(person.getId()));
	}
	if (person.getfName() != null && !person.getfName().equals(""))
	{
	    res.put("FirstName", person.getfName());
	}

	if (person.getlName() != null && !person.getlName().equals(""))
	{
	    res.put("LastName", person.getlName());
	}

	if (person.getSex() != null && !person.getSex().equals(""))
	{
	    res.put("sex", person.getSex());
	}

	if (person.getAge() != 0)
	{
	    res.put("age", String.valueOf(person.getAge()));
	}

	return res;

    }

}
